﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using AtletIS.Shared.Data;
using System.Data;

namespace AtletIS.Shared.Helpers
{
    public static class DatabaseHelper
    {
        private static DateTime? lastChipUpdate;

        #region Filling

        #region Categories

        public static void FillCategoriesActive(aDbDataSet.CategoriesDataTable dt)
        {
            string sql = "SELECT * FROM [Categories] WHERE [Active] = 1";
            SqlCommand cmd = new SqlCommand(sql);
            FillTable(dt, cmd);
        }

        #endregion

        #region Disciplines

        public static void FillDisciplinesActive(aDbDataSet.DisciplinesDataTable dt)
        {
            string sql = "SELECT * FROM [Disciplines] WHERE [Active] = 1";
            SqlCommand cmd = new SqlCommand(sql);
            FillTable(dt, cmd);
        }

        #endregion

        #region ChipTags

        public static void FillChipTagsByChipTagsGroupId(aDbDataSet.ChipTagsDataTable dt, int chipTagsGroupsId)
        {
            string sql = "SELECT * FROM [ChipTags] WHERE [ChipTagsGroupId] = @ChipTagsGroupId";

            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ChipTagsGroupId", System.Data.SqlDbType.Int);
            cmd.Parameters["@ChipTagsGroupId"].Value = chipTagsGroupsId;

            FillTable(dt, cmd);
        }

        #endregion

        #region Clubs

        public static void FillClubsActive(aDbDataSet.ClubsDataTable dt)
        {
            string sql = "SELECT * FROM [Clubs] WHERE [Active] = 1 ORDER BY [City], [Name]";
            SqlCommand cmd = new SqlCommand(sql);
            FillTable(dt, cmd);
        }

        #endregion

        #region Competitors

        public static void FillCompetitorsByClubId(aDbDataSet.CompetitorsDataTable dt, string clubId)
        {
            string sql = "SELECT * FROM [Competitors] WHERE [ClubId] = @ClubId AND [Active] = 1 ORDER BY [Surname], [FirstName]";

            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ClubId", System.Data.SqlDbType.VarChar, 5);
            cmd.Parameters["@ClubId"].Value = clubId;

            FillTable(dt, cmd);
        }

        public static void FillCompetitorsByCountryId(aDbDataSet.CompetitorsDataTable dt, string countryId)
        {
            string sql = "SELECT * FROM [Competitors] WHERE [CountryId] = @CountryId AND [Active] = 1 ORDER BY [Surname], [FirstName]";

            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@CountryId", System.Data.SqlDbType.VarChar, 3);
            cmd.Parameters["@CountryId"].Value = countryId;

            FillTable(dt, cmd);
        }

        #endregion

        #region ExtTexts

        public static void FillExtTextsByDisciplineListId(aDbDataSet.ExtTextsDataTable dt, int disciplineListId)
        {
            string sql = "SELECT * FROM [ExtTexts] WHERE [DisciplineListId] = @DisciplineListId AND [Active] = 1";

            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@DisciplineListId", System.Data.SqlDbType.Int);
            cmd.Parameters["@DisciplineListId"].Value = disciplineListId;

            FillTable(dt, cmd);
        }

        public static void FillExtTextsByPerformanceId(aDbDataSet.ExtTextsDataTable dt, int performanceId)
        {
            string sql = "SELECT * FROM [ExtTexts] WHERE [PerformanceId] = @PerformanceId  AND [Active] = 1";

            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@PerformanceId", System.Data.SqlDbType.Int);
            cmd.Parameters["@PerformanceId"].Value = performanceId;

            FillTable(dt, cmd);
        }

        #endregion

        #region ExtTextsCodes

        public static void FillExtTextsCodesForDisciplineList(aDbDataSet.ExtTextsCodesDataTable dt)
        {
            string sql = "SELECT * FROM [ExtTextsCodes] WHERE [DisciplineList] = 1";

            SqlCommand cmd = new SqlCommand(sql);

            FillTable(dt, cmd);
        }

        public static void FillExtTextsCodesForPerformance(aDbDataSet.ExtTextsCodesDataTable dt)
        {
            string sql = "SELECT * FROM [ExtTextsCodes] WHERE [Performance] = 1";

            SqlCommand cmd = new SqlCommand(sql);

            FillTable(dt, cmd);
        }

        #endregion

        #region ChipData

        public static void FillChipDataByStartNumber(aDbDataSet.ChipDataDataTable dt, int startNumber, int competitionId)
        {
            string sql = "EXEC GetChipDataByStartNumber @StartNumber, @CompetitionId ";

            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@StartNumber", System.Data.SqlDbType.Int);
            cmd.Parameters["@StartNumber"].Value = startNumber;
            cmd.Parameters.Add("@CompetitionId", System.Data.SqlDbType.Int);
            cmd.Parameters["@CompetitionId"].Value = competitionId;

            FillTable(dt, cmd);
        }

        #endregion

        #region PreChipData

        public static void FillPreChipData(aDbDataSet.PreChipDataDataTable dt, int disciplineListId)
        {
            DataTable dtDisciplineList = DatabaseHelper.GetDisciplineListById(disciplineListId);
            
            string sql = "SELECT * FROM PreChipData WHERE CompetitionId = @CompetitionId AND MeasurementId = @MeasurementId ";
            sql += "AND StartNumber IN (SELECT StartNumber FROM Performances WHERE DisciplineListId = @DisciplineListId)";

            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.AddWithValue("@CompetitionId", dtDisciplineList.Rows[0]["CompetitionId"]);
            cmd.Parameters.AddWithValue("@MeasurementId", dtDisciplineList.Rows[0]["MeasurementId"]);
            cmd.Parameters.AddWithValue("@DisciplineListId", disciplineListId);

            FillTable(dt, cmd);
        }

        #endregion

        #region CheckPoints

        public static void FillCheckPointsByDisciplineListId(aDbDataSet.CheckPointsDataTable dt, int disciplineListId)
        {
            string sql = "SELECT * FROM [CheckPoints] WHERE [DisciplineListId] = @DisciplineListId ORDER BY [OrderInDiscipline]";

            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@DisciplineListId", System.Data.SqlDbType.Int);
            cmd.Parameters["@DisciplineListId"].Value = disciplineListId;

            FillTable(dt, cmd);
        }

        #endregion

        #region Performances

        public static void FillPerformancesByCompetitionId(aDbDataSet.PerformancesDataTable dt, int competitionId)
        {
            string sql = "SELECT p.*, cl.Name AS ClubName, co.Name AS CountryName, dl.[Order], d.Code as DisciplineCode FROM Performances p " +
                "INNER JOIN DisciplineList dl ON dl.ID = p.DisciplineListId " +
                "INNER JOIN Disciplines d ON d.ID = dl.DisciplineId " +
                "LEFT OUTER JOIN Clubs cl ON cl.ID = p.Club " +
                "LEFT OUTER JOIN Countries co ON co.ID = p.CountryId " +
                "WHERE dl.CompetitionId = @CompetitionId AND p.Active = 1 " +
                "ORDER BY p.Code, p.Rank, p.Result, p.Lane, p.StartNumber";

            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@CompetitionId", System.Data.SqlDbType.Int);
            cmd.Parameters["@CompetitionId"].Value = competitionId;

            FillTable(dt, cmd);
        }

        public static void FillPerformancesByDisciplineListId(aDbDataSet.PerformancesDataTable dt, int disciplineListId)
        {
            string sql = "SELECT p.*, cl.Name AS ClubName, co.Name AS CountryName, d.Code as DisciplineCode FROM Performances p " +
                "INNER JOIN DisciplineList dl ON dl.ID = p.DisciplineListId " +
                "INNER JOIN Disciplines d ON d.ID = dl.DisciplineId " +
                "LEFT OUTER JOIN Clubs cl ON cl.ID = p.Club " +
                "LEFT OUTER JOIN Countries co ON co.ID = p.CountryId " +
                "WHERE p.DisciplineListId = @DisciplineListId AND p.Active = 1 " +
                "ORDER BY p.Code, p.Rank, p.Result, p.Lane, p.StartNumber";

            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@DisciplineListId", System.Data.SqlDbType.Int);
            cmd.Parameters["@DisciplineListId"].Value = disciplineListId;

            FillTable(dt, cmd);
        }

        public static void FillPerformancesByDisciplineListIdCountryId(aDbDataSet.PerformancesDataTable dt, int disciplineListId, string countryId)
        {
            string sql = "SELECT p.*, cl.Name AS ClubName, co.Name AS CountryName, d.Code as DisciplineCode FROM Performances p " +
                "INNER JOIN DisciplineList dl ON dl.ID = p.DisciplineListId " +
                "INNER JOIN Disciplines d ON d.ID = dl.DisciplineId " +
                "LEFT OUTER JOIN Clubs cl ON cl.ID = p.Club " +
                "LEFT OUTER JOIN Countries co ON co.ID = p.CountryId " +
                "WHERE p.DisciplineListId = @DisciplineListId AND p.CountryId = @CountryId AND p.Active = 1 " +
                "ORDER BY p.Code, p.Rank, p.Result, p.Lane, p.StartNumber";

            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@DisciplineListId", System.Data.SqlDbType.Int);
            cmd.Parameters["@DisciplineListId"].Value = disciplineListId;
            cmd.Parameters.Add("@CountryId", System.Data.SqlDbType.VarChar, 3);
            cmd.Parameters["@CountryId"].Value = countryId;

            FillTable(dt, cmd);
        }

        public static void FillPerformancesByDisciplineListIdClubId(aDbDataSet.PerformancesDataTable dt, int disciplineListId, string clubId)
        {
            string sql = "SELECT p.*, cl.Name AS ClubName, co.Name AS CountryName, d.Code as DisciplineCode FROM Performances p " +
                "INNER JOIN DisciplineList dl ON dl.ID = p.DisciplineListId " +
                "INNER JOIN Disciplines d ON d.ID = dl.DisciplineId " +
                "LEFT OUTER JOIN Clubs cl ON cl.ID = p.Club " +
                "LEFT OUTER JOIN Countries co ON co.ID = p.CountryId " +
                "WHERE p.DisciplineListId = @DisciplineListId AND p.Club = @ClubId AND p.Active = 1 " +
                "ORDER BY p.Code, p.Rank, p.Result, p.Lane, p.StartNumber";

            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@DisciplineListId", System.Data.SqlDbType.Int);
            cmd.Parameters["@DisciplineListId"].Value = disciplineListId;
            cmd.Parameters.Add("@ClubId", System.Data.SqlDbType.VarChar, 5);
            cmd.Parameters["@ClubId"].Value = clubId;

            FillTable(dt, cmd);
        }

        #endregion

        #region DisciplineList

        public static void FillDisciplineListByCompetitionId(aDbDataSet.DisciplineListDataTable dt, int id)
        {
            string sql = "SELECT dl.*, d.Code as DisciplineCode, d.ResultCode " 
                + "FROM DisciplineList dl "
                + "INNER JOIN Disciplines d ON d.ID = dl.DisciplineId "
                + "WHERE dl.CompetitionId = @Id AND dl.ParentId = 0 AND dl.Active = 1 ORDER BY dl.[Order]";

            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@Id", System.Data.SqlDbType.Int);
            cmd.Parameters["@Id"].Value = id;

            FillTable(dt, cmd);
        }

        #endregion

        #region Competitions

        public static void FillCompetitionsActive(aDbDataSet.CompetitionsDataTable dt)
        {
            string sql = "SELECT * FROM [Competitions] WHERE [Active] = 1";
            SqlCommand cmd = new SqlCommand(sql);
            FillTable(dt, cmd);
        }

        public static void FillCompetitionsByYear(aDbDataSet.CompetitionsDataTable dt, int year)
        {
            string sql = string.Format("SELECT * FROM [Competitions] WHERE DATEPART(YEAR, [DateFrom]) = {0}", year);
            SqlCommand cmd = new SqlCommand(sql);
            FillTable(dt, cmd);
        }

        #endregion

        #endregion

        private static void FillTable(DataTable dt, SqlCommand cmd)
        {
            dt.Clear();
            SqlConnection conn = new SqlConnection(Properties.Settings.Default.atletisConnectionString);
            cmd.Connection = conn;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
        }

        #region Getting

        #region Competitions

        public static aDbDataSet.CompetitionsDataTable GetCompetitionsById(int id)
        {
            string sql = "SELECT * FROM [Competitions] WHERE [ID] = @ID";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = id;

            aDbDataSet.CompetitionsDataTable dt = new aDbDataSet.CompetitionsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.CompetitionsDataTable GetCompetitionsByWebId(int id)
        {
            string sql = "SELECT * FROM [Competitions] WHERE [WebId] = @ID";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = id;

            aDbDataSet.CompetitionsDataTable dt = new aDbDataSet.CompetitionsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.CompetitionsDataTable GetCompetitionsActive()
        {
            string sql = "SELECT * FROM [Competitions] WHERE [Active] = 1";
            SqlCommand cmd = new SqlCommand(sql);

            aDbDataSet.CompetitionsDataTable dt = new aDbDataSet.CompetitionsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        #endregion

        #region DisciplineList

        public static aDbDataSet.DisciplineListDataTable GetDisciplineListById(int id)
        {
            string sql = "SELECT dl.*, d.Code as DisciplineCode, d.ResultCode " 
                + "FROM DisciplineList dl "
                + "INNER JOIN Disciplines d ON d.ID = dl.DisciplineId "
                + "WHERE dl.ID = @ID";
            
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = id;

            aDbDataSet.DisciplineListDataTable dt = new aDbDataSet.DisciplineListDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        /// <summary>
        ///  Načíta disciplíny pre preteky, len hlavné a aktívne
        /// </summary>
        /// <param name="competitionId">Id preteku</param>
        public static aDbDataSet.DisciplineListDataTable GetDisciplineListByCompetitionIdActive(int competitionId)
        {
            string sql = "SELECT dl.*, d.Code as DisciplineCode, d.ResultCode " 
                + "FROM DisciplineList dl "
                + "INNER JOIN Disciplines d ON d.ID = dl.DisciplineId "
                + "WHERE dl.CompetitionId = @ID AND dl.ParentId = 0 AND dl.Active = 1 " 
                + "ORDER BY dl.[Order]";
            
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = competitionId;

            aDbDataSet.DisciplineListDataTable dt = new aDbDataSet.DisciplineListDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        /// <summary>
        ///  Načíta disciplíny pre preteky, len hlavné
        /// </summary>
        /// <param name="competitionId">Id preteku</param>
        public static aDbDataSet.DisciplineListDataTable GetDisciplineListByCompetitionIdAll(int competitionId)
        {
            string sql = "SELECT dl.*, d.Code as DisciplineCode, d.ResultCode "
                + "FROM DisciplineList dl "
                + "INNER JOIN Disciplines d ON d.ID = dl.DisciplineId "
                + "WHERE dl.CompetitionId = @ID AND dl.ParentId = 0 "
                + "ORDER BY dl.[Order]";

            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = competitionId;

            aDbDataSet.DisciplineListDataTable dt = new aDbDataSet.DisciplineListDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        /// <summary>
        ///  Načíta disciplíny pre preteky, len subdisciplíny
        /// </summary>
        /// <param name="competitionId">Id preteku</param>
        public static aDbDataSet.DisciplineListDataTable GetDisciplineListByCompetitionIdChildrenActive(int competitionId)
        {
            string sql = "SELECT dl.*, d.Code as DisciplineCode, d.ResultCode " 
                + "FROM DisciplineList dl "
                + "INNER JOIN Disciplines d ON d.ID = dl.DisciplineId "
                + "WHERE dl.CompetitionId = @ID AND dl.ParentId > 0 AND dl.Active = 1 " 
                + "ORDER BY dl.[Order], dl.[SecondaryOrder]";
            
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = competitionId;

            aDbDataSet.DisciplineListDataTable dt = new aDbDataSet.DisciplineListDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        /// <summary>
        ///  Načíta disciplíny pre preteky, všetky
        /// </summary>
        /// <param name="competitionId">Id preteku</param>
        public static aDbDataSet.DisciplineListDataTable GetAllDisciplineListByCompetitionIdAll(int competitionId)
        {
            string sql = "SELECT dl.*, d.Code as DisciplineCode, d.ResultCode " 
                + "FROM DisciplineList dl "
                + "INNER JOIN Disciplines d ON d.ID = dl.DisciplineId "
                + "WHERE dl.CompetitionId = @ID "
                + "ORDER BY dl.[Order], dl.[SecondaryOrder]";
            
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = competitionId;

            aDbDataSet.DisciplineListDataTable dt = new aDbDataSet.DisciplineListDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        /// <summary>
        ///  Načíta subdisciplíny pre disciplínu
        /// </summary>
        /// <param name="competitionId">Id nadradenej disciplíny</param>
        public static aDbDataSet.DisciplineListDataTable GetDisciplineListByParentIdActive(int parentId)
        {
            string sql = "SELECT dl.*, d.Code as DisciplineCode, d.ResultCode " 
                + "FROM DisciplineList dl "
                + "INNER JOIN Disciplines d ON d.ID = dl.DisciplineId "
                + "WHERE dl.ParentId = @ID AND dl.Active = 1 " 
                + "ORDER BY dl.[SecondaryOrder], dl.[Name]";
            
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = parentId;

            aDbDataSet.DisciplineListDataTable dt = new aDbDataSet.DisciplineListDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        /// <summary>
        ///  Načíta subdisciplíny pre disciplínu
        /// </summary>
        /// <param name="competitionId">Id nadradenej disciplíny</param>
        public static aDbDataSet.DisciplineListDataTable GetDisciplineListByParentIdAll(int parentId)
        {
            string sql = "SELECT dl.*, d.Code as DisciplineCode, d.ResultCode "
                + "FROM DisciplineList dl "
                + "INNER JOIN Disciplines d ON d.ID = dl.DisciplineId "
                + "WHERE dl.ParentId = @ID "
                + "ORDER BY dl.[SecondaryOrder], dl.[Name]";

            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = parentId;

            aDbDataSet.DisciplineListDataTable dt = new aDbDataSet.DisciplineListDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        /// <summary>
        ///  Načíta všetky disciplíny pre preteky pre Sync.
        /// </summary>
        /// <param name="competitionId">Id preteku</param>
        public static aDbDataSet.DisciplineListDataTable GetDisciplineListByCompetitionIdSync(int competitionId)
        {
            string sql = "SELECT * FROM DisciplineList WHERE CompetitionId = @ID";

            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", competitionId);

            aDbDataSet.DisciplineListDataTable dt = new aDbDataSet.DisciplineListDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        /// <summary>
        ///  Načíta všetky disciplíny pre preteky pre Sync.
        /// </summary>
        /// <param name="competitionId">Id preteku</param>
        public static int GetDisciplineListNewAutoOrder(int competitionId)
        {
            try
            {
                string sql = "SELECT MAX(AutoOrder) as AutoOrder FROM DisciplineList WHERE CompetitionId = @ID";

                SqlCommand cmd = new SqlCommand(sql);
                cmd.Parameters.Add("@ID", competitionId);

                var dt = new DataTable();
                FillTable(dt, cmd);

                if (dt != null && dt.Rows.Count > 0)
                    return Convert.ToInt32(dt.Rows[0]["AutoOrder"]) + 1;
            }
            catch (Exception ex) 
            {
                Console.Write(ex.ToString());
            }

            return 101;
        }

        #endregion

        #region Categories

        public static aDbDataSet.CategoriesDataTable GetCategoriesById(int id)
        {
            string sql = "SELECT * FROM [Categories] WHERE [ID] = @ID";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = id;

            aDbDataSet.CategoriesDataTable dt = new aDbDataSet.CategoriesDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.CategoriesDataTable GetCategoriesActive()
        {
            string sql = "SELECT * FROM [Categories] WHERE [Active] = 1 ORDER BY [Name]";
            SqlCommand cmd = new SqlCommand(sql);

            aDbDataSet.CategoriesDataTable dt = new aDbDataSet.CategoriesDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        #endregion

        #region Disciplines

        public static aDbDataSet.DisciplinesDataTable GetDisciplinesById(int id)
        {
            string sql = "SELECT * FROM [Disciplines] WHERE [ID] = @ID";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = id;

            aDbDataSet.DisciplinesDataTable dt = new aDbDataSet.DisciplinesDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.DisciplinesDataTable GetDisciplinesActive()
        {
            string sql = "SELECT * FROM [Disciplines] WHERE [Active] = 1 ORDER BY [Name]";
            SqlCommand cmd = new SqlCommand(sql);

            aDbDataSet.DisciplinesDataTable dt = new aDbDataSet.DisciplinesDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.DisciplinesDataTable GetDisciplinesByCompetitionId(int competitionId)
        {
            string sql = "SELECT DISTINCT d.* FROM [Disciplines] AS d " + 
                "INNER JOIN DisciplineList AS dl ON dl.DisciplineId = d.ID " +
                "WHERE dl.CompetitionId = @CompetitionId " +
                "ORDER BY d.Name";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@CompetitionId", System.Data.SqlDbType.Int);
            cmd.Parameters["@CompetitionId"].Value = competitionId;

            aDbDataSet.DisciplinesDataTable dt = new aDbDataSet.DisciplinesDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        #endregion

        #region Performances

        public static aDbDataSet.PerformancesDataTable GetPerformancesById(int id)
        {
            string sql = "SELECT * FROM [Performances] WHERE [ID] = @ID AND [Active] = 1";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = id;

            aDbDataSet.PerformancesDataTable dt = new aDbDataSet.PerformancesDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.PerformancesDataTable GetPerformancesByDisciplineListId(int disciplineListId)
        {
            string sql = "SELECT p.*, cl.Name AS ClubName, co.Name AS CountryName, d.Code as DisciplineCode, d.ResultCode FROM Performances p " +
                "INNER JOIN DisciplineList dl ON dl.ID = p.DisciplineListId " +
                "INNER JOIN Disciplines d ON d.ID = dl.DisciplineId " +
                "LEFT OUTER JOIN Clubs cl ON cl.ID = p.Club " +
                "LEFT OUTER JOIN Countries co ON co.ID = p.CountryId " +
                "WHERE p.DisciplineListId = @ID AND p.Active = 1 " +
                "ORDER BY p.Code, p.Rank, p.Result, p.Lane, p.StartNumber";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = disciplineListId;

            aDbDataSet.PerformancesDataTable dt = new aDbDataSet.PerformancesDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.PerformancesDataTable GetPerformancesByDisciplineListIdSync(int competitionId)
        {
            string sql = "SELECT p.* FROM Performances p " +
                "INNER JOIN DisciplineList dl ON dl.ID = p.DisciplineListId " +
                "WHERE dl.CompetitionId = @ID ";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", competitionId);

            aDbDataSet.PerformancesDataTable dt = new aDbDataSet.PerformancesDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.PerformancesDataTable GetPerformancesByCompetitionIdClubId(int competitionId, string clubId)
        {
            string sql = "SELECT p.*, cl.Name AS ClubName, co.Name AS CountryName, dl.[Order], dl.Name, dl.Relay, d.Code as DisciplineCode FROM [Performances] p " +
                "INNER JOIN [DisciplineList] dl ON dl.ID = p.DisciplineListId " +
                "INNER JOIN Disciplines d ON d.ID = dl.DisciplineId " +
                "LEFT OUTER JOIN [Clubs] cl ON cl.ID = p.Club " + 
                "LEFT OUTER JOIN [Countries] co ON co.ID = p.CountryId " +
                "WHERE dl.CompetitionId = @CompetitionId AND p.Club = @ClubId AND p.Active = 1 " +
                "ORDER BY p.Surname, p.FirstName";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@CompetitionId", System.Data.SqlDbType.Int);
            cmd.Parameters["@CompetitionId"].Value = competitionId;
            cmd.Parameters.Add("@ClubId", System.Data.SqlDbType.VarChar, 5);
            cmd.Parameters["@ClubId"].Value = clubId;

            aDbDataSet.PerformancesDataTable dt = new aDbDataSet.PerformancesDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.PerformancesDataTable GetPerformancesByCompetitionIdCountryId(int competitionId, string countryId)
        {
            string sql = "SELECT p.*, cl.Name AS ClubName, co.Name AS CountryName, dl.[Order], dl.Name, dl.Relay, d.Code as DisciplineCode FROM [Performances] p " +
                "INNER JOIN [DisciplineList] dl ON dl.ID = p.DisciplineListId " +
                "INNER JOIN Disciplines d ON d.ID = dl.DisciplineId " +
                "LEFT OUTER JOIN [Clubs] cl ON cl.ID = p.Club " +
                "LEFT OUTER JOIN [Countries] co ON co.ID = p.CountryId " +
                "WHERE dl.CompetitionId = @CompetitionId AND p.CountryId = @CountryId AND p.Active = 1 " +
                "ORDER BY p.Surname, p.FirstName";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@CompetitionId", System.Data.SqlDbType.Int);
            cmd.Parameters["@CompetitionId"].Value = competitionId;
            cmd.Parameters.Add("@CountryId", System.Data.SqlDbType.VarChar, 5);
            cmd.Parameters["@CountryId"].Value = countryId;

            aDbDataSet.PerformancesDataTable dt = new aDbDataSet.PerformancesDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.PerformancesDataTable GetPerformancesByCompetitionId(int competitionId)
        {
            string sql = "SELECT p.*, dl.[Order], d.Code as DisciplineCode FROM Performances p " +
                "INNER JOIN DisciplineList dl ON p.DisciplineListId = dl.ID " +
                "INNER JOIN Disciplines d ON d.ID = dl.DisciplineId " +
                "WHERE dl.CompetitionId = @CompetitionId AND p.Active = 1 " +
                "ORDER BY p.CountryId, p.Club, p.Surname";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@CompetitionId", System.Data.SqlDbType.Int);
            cmd.Parameters["@CompetitionId"].Value = competitionId;

            aDbDataSet.PerformancesDataTable dt = new aDbDataSet.PerformancesDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.PerformancesDataTable GetPerformancesByCompetitionIdAll(int competitionId)
        {
            string sql = "SELECT p.*, dl.[Order], d.Code as DisciplineCode FROM Performances p " +
                "INNER JOIN DisciplineList dl ON p.DisciplineListId = dl.ID " +
                "INNER JOIN Disciplines d ON d.ID = dl.DisciplineId " +
                "WHERE dl.CompetitionId = @CompetitionId " +
                "ORDER BY p.CountryId, p.Club, p.Surname";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@CompetitionId", System.Data.SqlDbType.Int);
            cmd.Parameters["@CompetitionId"].Value = competitionId;

            aDbDataSet.PerformancesDataTable dt = new aDbDataSet.PerformancesDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static DataTable GetPerformancesDistinctClubs(int competitionId)
        {
            string sql = "SELECT DISTINCT p.Club, c.Name as ClubName, c.City FROM Performances p " +
                "INNER JOIN DisciplineList dl ON p.DisciplineListId = dl.ID " +
                "INNER JOIN Clubs c ON c.ID = p.Club " +
                "WHERE dl.CompetitionId = @CompetitionId AND p.Active = 1 " +
                "ORDER BY c.City, c.Name";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@CompetitionId", System.Data.SqlDbType.Int);
            cmd.Parameters["@CompetitionId"].Value = competitionId;

            DataTable dt = new DataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static DataTable GetPerformancesDistinctCountries(int competitionId)
        {
            string sql = "SELECT DISTINCT p.CountryId, c.Name FROM Performances p " +
                "INNER JOIN Countries c ON p.CountryId = c.ID " +
                "INNER JOIN DisciplineList dl ON p.DisciplineListId = dl.ID " +
                "WHERE dl.CompetitionId = @CompetitionId AND p.Active = 1 " +
                "ORDER BY c.Name";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@CompetitionId", System.Data.SqlDbType.Int);
            cmd.Parameters["@CompetitionId"].Value = competitionId;

            DataTable dt = new DataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.PerformancesDataTable GetPerformancesByCompetitionIdStartNumber(int competitionId, int startNumber)
        {
            string sql = "SELECT p.*, dl.Name FROM Performances p " + 
                "INNER JOIN DisciplineList dl ON p.DisciplineListId = dl.ID " +
                "WHERE dl.CompetitionId = @CompetitionId AND p.StartNumber = @StartNumber AND p.Active = 1";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@CompetitionId", System.Data.SqlDbType.Int);
            cmd.Parameters["@CompetitionId"].Value = competitionId;
            cmd.Parameters.Add("@StartNumber", System.Data.SqlDbType.Int);
            cmd.Parameters["@StartNumber"].Value = startNumber;

            aDbDataSet.PerformancesDataTable dt = new aDbDataSet.PerformancesDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.PerformancesDataTable GetPerformancesByCompetitionIdLikeSurname(int competitionId, string surname)
        {
            string sql = "SELECT p.*, dl.CompetitionId, dl.Name FROM Performances p " + 
                "INNER JOIN DisciplineList dl ON p.DisciplineListId = dl.ID " +
                "WHERE dl.CompetitionId = @CompetitionId AND p.Surname LIKE @Surname AND p.Active = 1";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@CompetitionId", System.Data.SqlDbType.Int);
            cmd.Parameters["@CompetitionId"].Value = competitionId;
            cmd.Parameters.Add("@Surname", System.Data.SqlDbType.VarChar, 50);
            cmd.Parameters["@Surname"].Value = surname;

            aDbDataSet.PerformancesDataTable dt = new aDbDataSet.PerformancesDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.PerformancesDataTable GetPerformancesByDisciplineId(int competitionId, int disciplineId)
        {
            string sql = "EXEC [GetPerformancesByDisciplineId] @DisciplineId, @CompetitionId";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@DisciplineId", System.Data.SqlDbType.Int);
            cmd.Parameters["@DisciplineId"].Value = disciplineId;
            cmd.Parameters.Add("@CompetitionId", System.Data.SqlDbType.Int);
            cmd.Parameters["@CompetitionId"].Value = competitionId;

            aDbDataSet.PerformancesDataTable dt = new aDbDataSet.PerformancesDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        #endregion

        #region CheckPoints

        public static aDbDataSet.CheckPointsDataTable GetCheckPointsByDisciplineListId(int disciplineListId)
        {
            string sql = "SELECT * FROM [CheckPoints] WHERE [DisciplineListId] = @ID ORDER BY [OrderInDiscipline]";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = disciplineListId;

            aDbDataSet.CheckPointsDataTable dt = new aDbDataSet.CheckPointsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.CheckPointsDataTable GetCheckPointsByDisciplineListIdAndOrder(int disciplineListId, int order)
        {
            string sql = "SELECT * FROM [CheckPoints] WHERE [DisciplineListId] = @ID AND [OrderInDiscipline] = @Order";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = disciplineListId;
            cmd.Parameters.Add("@Order", System.Data.SqlDbType.Int);
            cmd.Parameters["@Order"].Value = order;

            aDbDataSet.CheckPointsDataTable dt = new aDbDataSet.CheckPointsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.CheckPointsDataTable GetCheckPointsByDisciplineListIdStart(int disciplineListId)
        {
            string sql = "SELECT * FROM [CheckPoints] WHERE [DisciplineListId] = @ID AND [Code] = 0";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = disciplineListId;

            aDbDataSet.CheckPointsDataTable dt = new aDbDataSet.CheckPointsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.CheckPointsDataTable GetCheckPointsByDisciplineListIdFinish(int disciplineListId)
        {
            string sql = "SELECT * FROM [CheckPoints] WHERE [DisciplineListId] = @ID AND [Code] = 1";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = disciplineListId;

            aDbDataSet.CheckPointsDataTable dt = new aDbDataSet.CheckPointsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.CheckPointsDataTable GetCheckPointsByDisciplineListIdActive(int disciplineListId, bool desc)
        {
            string sql = "SELECT * FROM [CheckPoints] WHERE [DisciplineListId] = @ID AND [Code] <> 4 ORDER BY [OrderInDiscipline]";
            if (desc)
            {
                sql += " DESC";
            }
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = disciplineListId;

            aDbDataSet.CheckPointsDataTable dt = new aDbDataSet.CheckPointsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        #endregion

        #region DisciplinesStatuses

        public static aDbDataSet.DisciplinesStatusesDataTable GetDisciplinesStatusesById(int id)
        {
            string sql = "SELECT * FROM [DisciplinesStatuses] WHERE [ID] = @ID";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = id;

            aDbDataSet.DisciplinesStatusesDataTable dt = new aDbDataSet.DisciplinesStatusesDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        #endregion

        #region ResultCodes

        public static aDbDataSet.ResultCodesDataTable GetResultCodesById(int id)
        {
            string sql = "SELECT * FROM [ResultCodes] WHERE [ID] = @ID";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = id;

            aDbDataSet.ResultCodesDataTable dt = new aDbDataSet.ResultCodesDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        #endregion

        #region RefPoints

        public static aDbDataSet.RefPointsDataTable GetRefPointsByDisciplineListId(int disciplineListId, int order)
        {
            string sql = "SELECT * FROM [RefPoints] WHERE [DisciplineListId] = @DisciplineListId AND [RefPointOrder] = @RefPointOrder";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@DisciplineListId", disciplineListId);
            cmd.Parameters.Add("@RefPointOrder", order);

            aDbDataSet.RefPointsDataTable dt = new aDbDataSet.RefPointsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        #endregion

        #region Countries

        public static aDbDataSet.CountriesDataTable GetCountriesById(string id)
        {
            string sql = "SELECT * FROM [Countries] WHERE [ID] = @ID";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.VarChar, 3);
            cmd.Parameters["@ID"].Value = id;

            aDbDataSet.CountriesDataTable dt = new aDbDataSet.CountriesDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        #endregion

        #region Clubs

        public static aDbDataSet.ClubsDataTable GetClubsById(string id)
        {
            string sql = "SELECT * FROM [Clubs] WHERE [ID] = @ID";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.VarChar, 5);
            cmd.Parameters["@ID"].Value = id;

            aDbDataSet.ClubsDataTable dt = new aDbDataSet.ClubsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.ClubsDataTable GetClubsActive()
        {
            string sql = "SELECT * FROM [Clubs] WHERE [Active] = 1 ORDER BY [City], [Name]";
            SqlCommand cmd = new SqlCommand(sql);

            aDbDataSet.ClubsDataTable dt = new aDbDataSet.ClubsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.ClubsDataTable GetClubsAll()
        {
            string sql = "SELECT * FROM [Clubs] ORDER BY [City], [Name]";
            SqlCommand cmd = new SqlCommand(sql);

            aDbDataSet.ClubsDataTable dt = new aDbDataSet.ClubsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        #endregion

        #region Competitors

        public static aDbDataSet.CompetitorsDataTable GetCompetitorsAll()
        {
            string sql = "SELECT * FROM [Competitors]";
            SqlCommand cmd = new SqlCommand(sql);

            aDbDataSet.CompetitorsDataTable dt = new aDbDataSet.CompetitorsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.CompetitorsDataTable GetCompetitorsById(int id)
        {
            string sql = "SELECT * FROM [Competitors] WHERE [ID] = @ID";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = id;

            aDbDataSet.CompetitorsDataTable dt = new aDbDataSet.CompetitorsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.CompetitorsDataTable GetCompetitorsByClubId(string clubId)
        {
            string sql = "SELECT * FROM [Competitors] WHERE [ClubId] = @ID AND [Active] = 1 ORDER BY [Surname], [FirstName]";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.VarChar, 5);
            cmd.Parameters["@ID"].Value = clubId;

            aDbDataSet.CompetitorsDataTable dt = new aDbDataSet.CompetitorsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.CompetitorsDataTable GetCompetitorsByCountryId(string countryId)
        {
            string sql = "SELECT * FROM [Competitors] WHERE [CountryId] = @ID AND [Active] = 1 ORDER BY [Surname], [FirstName]";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.VarChar, 3);
            cmd.Parameters["@ID"].Value = countryId;

            aDbDataSet.CompetitorsDataTable dt = new aDbDataSet.CompetitorsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        #endregion

        #region ExtTexts

        public static aDbDataSet.ExtTextsDataTable GetExtTextsByDisciplineListIdActive(int disciplineListId, string codes = "1,2")
        {
            string sql = "SELECT * FROM [ExtTexts] WHERE [DisciplineListId] = @ID AND [Active] = 1 AND Code IN (" + codes + ")";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = disciplineListId;

            aDbDataSet.ExtTextsDataTable dt = new aDbDataSet.ExtTextsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.ExtTextsDataTable GetExtTextsByPerformanceIdActive(int performanceId)
        {
            string sql = "SELECT * FROM [ExtTexts] WHERE [PerformanceId] = @ID AND [Active] = 1";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = performanceId;

            aDbDataSet.ExtTextsDataTable dt = new aDbDataSet.ExtTextsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        

        public static aDbDataSet.ExtTextsDataTable GetExtTextsByPerformanceIdHeaderAll(int performanceId, string header)
        {
            string sql = "SELECT * FROM [ExtTexts] WHERE [PerformanceId] = @ID AND [Header] = @Header";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = performanceId;
            cmd.Parameters.Add("@Header", System.Data.SqlDbType.VarChar, 100);
            cmd.Parameters["@Header"].Value = header;

            aDbDataSet.ExtTextsDataTable dt = new aDbDataSet.ExtTextsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.ExtTextsDataTable GetExtTextsByParentDisciplineListId(int parentDisciplineListId)
        {
            string sql = "SELECT et.*, p.DisciplineListId AS ParentDisciplineListId FROM [ExtTexts] et " + 
                "INNER JOIN [Performances] p ON et.PerformanceId = p.ID " + 
                "WHERE p.DisciplineListId = @ID AND et.Active = 1";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = parentDisciplineListId;

            aDbDataSet.ExtTextsDataTable dt = new aDbDataSet.ExtTextsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.ExtTextsDataTable GetExtTextsByParentDisciplineListIdNumeric(int parentDisciplineListId)
        {
            string sql = "SELECT et.*, p.DisciplineListId AS ParentDisciplineListId FROM [ExtTexts] et " +
                "INNER JOIN [Performances] p ON et.PerformanceId = p.ID " +
                "WHERE p.DisciplineListId = @ID AND et.Active = 1 AND et.Text1 NOT LIKE '%[^0-9]%'";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = parentDisciplineListId;

            aDbDataSet.ExtTextsDataTable dt = new aDbDataSet.ExtTextsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        /// <summary>
        /// Načíta všetky doplňujúce texty k disciplínam v pretekoch pre Sync.
        /// </summary>
        public static aDbDataSet.ExtTextsDataTable GetExtTextsForDisciplineListByCompetitionIdSync(int competitionId)
        {
            string sql = "SELECT et.* FROM [ExtTexts] et ";
            sql += " INNER JOIN [DisciplineList] dl ON dl.ID = et.DisciplineListId ";
            sql += " WHERE dl.[CompetitionId] = @CompetitionId";

            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.AddWithValue("@CompetitionId", competitionId);

            aDbDataSet.ExtTextsDataTable dt = new aDbDataSet.ExtTextsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        /// <summary>
        /// Načíta všetky doplňujúce texty k výkonom v pretekoch pre Sync.
        /// </summary>
        public static aDbDataSet.ExtTextsDataTable GetExtTextsForPerformancesByCompetitionIdSync(int competitionId)
        {
            string sql = "SELECT et.* FROM [ExtTexts] et ";
            sql += "INNER JOIN [Performances] p ON p.ID = et.PerformanceId ";
            sql += "INNER JOIN [DisciplineList] dl ON dl.ID = p.DisciplineListId ";
            sql += "WHERE dl.[CompetitionId] = @CompetitionId";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.AddWithValue("@CompetitionId", competitionId);

            aDbDataSet.ExtTextsDataTable dt = new aDbDataSet.ExtTextsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        #endregion

        #region ExtTextsCodes

        public static aDbDataSet.ExtTextsCodesDataTable GetExtTextsCodesById(int id)
        {
            string sql = "SELECT * FROM [ExtTextsCodes] WHERE [ID] = @ID";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.AddWithValue("@ID", id);

            aDbDataSet.ExtTextsCodesDataTable dt = new aDbDataSet.ExtTextsCodesDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        #endregion

        #region PreChipData

        public static aDbDataSet.PreChipDataDataTable GetPreChipDataByCompetitionId(int competitionId)
        {
            string sql = "SELECT * FROM [PreChipData] WHERE [CompetitionId] = @CompetitionId";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.AddWithValue("@CompetitionId", competitionId);

            aDbDataSet.PreChipDataDataTable dt = new aDbDataSet.PreChipDataDataTable();
            FillTable(dt, cmd);

            return dt;
        }

        #endregion

        #region ChipData

        public static aDbDataSet.ChipDataDataTable GetChipDataByCompetitionId(int competitionId)
        {
            DateTime now = DateTime.Now;

            if (!lastChipUpdate.HasValue)
            {
                lastChipUpdate = new DateTime(1900, 1, 1);
            }
            
            string sql = "SELECT * FROM [ChipData] WHERE [CompetitionId] = @CompetitionId AND [ChipDateTime] >= @Max ORDER BY [ChipDateTime] DESC";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.AddWithValue("@CompetitionId", competitionId);
            cmd.Parameters.AddWithValue("@Max", lastChipUpdate);

            aDbDataSet.ChipDataDataTable dt = new aDbDataSet.ChipDataDataTable();
            FillTable(dt, cmd);

            if (dt.Rows.Count > 0)
            {
                lastChipUpdate = Convert.ToDateTime(dt.Rows[0]["ChipDateTime"]);
            }

            return dt;
        }

        public static aDbDataSet.ChipDataDataTable GetChipDataByCheckPointId(int checkPointId)
        {
            string sql = "EXEC [GetChipDataByCheckPointId] @CheckPointId";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@CheckPointId", System.Data.SqlDbType.Int);
            cmd.Parameters["@CheckPointId"].Value = checkPointId;

            aDbDataSet.ChipDataDataTable dt = new aDbDataSet.ChipDataDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.ChipDataDataTable GetChipDataByStartNumber(int startNumber, int competitionId)
        {
            string sql = "EXEC [GetChipDataByStartNumber] @StartNumber, @CompetitionId";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@StartNumber", System.Data.SqlDbType.Int);
            cmd.Parameters["@StartNumber"].Value = startNumber;
            cmd.Parameters.Add("@CompetitionId", System.Data.SqlDbType.Int);
            cmd.Parameters["@CompetitionId"].Value = competitionId;

            aDbDataSet.ChipDataDataTable dt = new aDbDataSet.ChipDataDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static aDbDataSet.ChipDataDataTable GetChipDataBoardData(int competitionId)
        {
            string sql = "EXEC [GetBoardData] @CompetitionId";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@CompetitionId", System.Data.SqlDbType.Int);
            cmd.Parameters["@CompetitionId"].Value = competitionId;

            aDbDataSet.ChipDataDataTable dt = new aDbDataSet.ChipDataDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static DataTable GetChipDataSplitsTable(int disciplineListId)
        {
            string sql = "EXEC [GetSplitsForDisciplineListId] @DisciplineListId";
            
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.AddWithValue("@DisciplineListId", disciplineListId);

            DataTable dt = new DataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static DataTable GetChipDataSplitsTableDiff(int disciplineListId)
        {
            string sql = "EXEC [GetSplitsForDisciplineListIdDiff] @DisciplineListId";
            
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.AddWithValue("@DisciplineListId", disciplineListId);

            DataTable dt = new DataTable();
            FillTable(dt, cmd);
            return dt;
        }

        public static DataTable GetPoints(int disciplineListId)
        {
            string sql = "EXEC [GetPointsForDisciplineListId] @DisciplineListId";

            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.AddWithValue("@DisciplineListId", disciplineListId);

            DataTable dt = new DataTable();
            FillTable(dt, cmd);
            return dt;
        }

        #endregion

        #region ChipTags

        public static aDbDataSet.ChipTagsDataTable GetChipTagsByGroupId(int groupId)
        {
            string sql = "SELECT * FROM [ChipTags] WHERE [ChipTagsGroupId] = @ID";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
            cmd.Parameters["@ID"].Value = groupId;

            aDbDataSet.ChipTagsDataTable dt = new aDbDataSet.ChipTagsDataTable();
            FillTable(dt, cmd);
            return dt;
        }

        #endregion

        #endregion

        #region Exec stored procedure

        public static void UpdatePreChipData(int disciplineListId, DateTime? start)
        {
            string sql = "EXEC UpdatePreChipData @DisciplineListId, @Start";

            SqlCommand cmd = new SqlCommand(sql);
            cmd.Parameters.AddWithValue("@DisciplineListId", disciplineListId);
            if (start.HasValue)
                cmd.Parameters.AddWithValue("@Start", start);
            else
                cmd.Parameters.AddWithValue("@Start", DBNull.Value);

            cmd.Connection = new SqlConnection(Properties.Settings.Default.atletisConnectionString);
            cmd.Connection.Open();
            if (cmd.Connection.State == ConnectionState.Open)
            {
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
        }

        #endregion

        public static void SetSyncAndWebId(string tableName, int id, int webId)
        {
            string sql = string.Format("UPDATE [{0}] SET [Sync] = 1, [WebId] = {1}, [Modified] = GETDATE() WHERE [ID] = {2}", tableName, webId, id);
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Connection = new SqlConnection(Properties.Settings.Default.atletisConnectionString);
            cmd.Connection.Open();
            if (cmd.Connection.State == ConnectionState.Open)
            {
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
        }

    }
}
